﻿using Dapper.Extensions;
using Microsoft.AspNetCore.Mvc;
using orm.dapper.api.Entities;

namespace orm.dapper.api.Controllers
{
    /// <summary>
    /// SQL 单表操作
    /// </summary>
    public class SingleOfSqlController(IDapper dapper) : DapperBaseController(dapper)
    {
        [HttpPost]
        public async Task<IActionResult> Insert()
        {
            string sql = $@"
                INSERT [{nameof(Order)}] (Number, TotalPrice, CreateTime, IsDeleted) 
                OUTPUT INSERTED.Id
                VALUES (@number, @totalPrice, @createTime, @isDeleted)
            ";

            var parms = new 
            {
                number = DateTime.Now.ToString("yyyyMMddHHmmss"),
                totalPrice = 0,
                createTime = DateTime.Now,
                isDeleted = 0
            };

            return Ok("Id：" + await Dapper.ExecuteScalarAsync<int>(sql, parms));
        }

        [HttpPatch]
        public async Task<IActionResult> Update(int id)
        {
            string sql = $@"
                UPDATE [{nameof(Order)}] 
                SET Number = @number, TotalPrice = @totalPrice, CreateTime = @createTime 
                WHERE Id = @id";

            var parms = new
            {
                id,
                number = DateTime.Now.ToString("yyyyMMddHHmmss"),
                totalPrice = 1,
                createTime = DateTime.Now
            };

            return Ok(await Dapper.ExecuteAsync(sql, parms) > 0);
        }

        [HttpDelete]
        public async Task<IActionResult> Delete(int id)
        {
            string sql = $@"DELETE FROM [{nameof(Order)}] WHERE Id = @id";
            return Ok(await Dapper.ExecuteAsync(sql, new { id }) > 0);
        }

        [HttpGet]
        public async Task<IActionResult> PageList(int pageIndex, int pageSize)
        {
            string sql = $@"
                SELECT * FROM [{nameof(Order)}] 
                WHERE IsDeleted = 0
                ORDER BY Id DESC
                OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;

                SELECT COUNT(*) FROM [{nameof(Order)}] 
                WHERE IsDeleted = 0";

            var parms = new 
            {
                skip = (pageIndex - 1) * pageSize,
                take = pageSize
            };
            
            var (result, result2) = await Dapper.QueryMultipleAsync<Order, int>(sql, parms);
            return Ok(new { rows = result, total = result2[0] });
        }

        [HttpGet]
        public async Task<IActionResult> PageList_v2(int pageIndex, int pageSize)
        {
            string countSql = $@"
                SELECT COUNT(*) FROM [{nameof(Order)}] 
                WHERE IsDeleted = 0";

            string dataSql = $@"
                SELECT * FROM [{nameof(Order)}] 
                WHERE IsDeleted = 0
                ORDER BY Id DESC";

            var result = await Dapper
                .QueryPageAsync<Order>(countSql, dataSql, pageIndex, pageSize);

            return Ok(new { rows = result.Result, total = result.TotalCount });
        }

        [HttpGet]
        public async Task<IActionResult> Detail(int id)
        {
            string sql = $@"SELECT * FROM [{nameof(Order)}] WHERE Id = @id";
            return Ok(await Dapper.QuerySingleOrDefaultAsync<Order>(sql, new { id }));
        }
    }
}
